# coding:utf-8
import datetime
import os
import sys
import time
from statistics import mean

import pandas as pd
import pymysql

# sys.path.append('..')
# from gwzaWorkspace import ruleFoulsReport as rfr

class sqlOperate:

    def __init__(self,host,port,user,passwd,db):
        self.host=host
        self.port=port
        self.user=user
        self.passwd=passwd
        self.db=db
        #self.name = name

    def sqlConnect(self):
        # 连接mysql
        self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset='utf8mb4')
        self.cursor = self.conn.cursor()
        print("连接成功")


    def reqRuleId(self,name):
        # 执行sql语句,通过围栏名称去查找围栏的id
        sql = "select id from {}.map_pos_rules where name=\"{}\"".format(self.db,name)
        self.cursor.execute(sql)
        print(sql)

        # 获取查询结果
        id = self.cursor.fetchall()[0][0]
        print(id)
        return id

    def reqHelmetVlt(self,id): # 去重显示多少安全帽违规 num是违规安全帽个数  devices是具体的安全帽id
        #id = self.reqRuleId()
        #id =616
        sql = "select DISTINCT device_id from  {}.vlt_fouls where rule_id={} ORDER BY device_id".format(self.db,id)
        print(sql)
        self.cursor.execute(sql)
        devices = self.cursor.fetchall()
        print(len(devices))
        num = len(devices)
        return num,devices



    def reqVltNum(self):
        #统计单个deviceId的违规条数
        id = self.reqRuleId()
        num,devices = self.reqHelmetVlt()
        arrNum = []
        for device in devices:
            #print(device[0])
            sql = "select * FROM {}.vlt_fouls where rule_id ={} AND device_id=\'{}\'".format(self.db,id,device[0])
            #print(sql)
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            #print(len(result))
            arrNum.append(len(result))
        return arrNum

    def reqVltDetails(self):  #显示该围栏所有违规信息 电子围栏违规
        id = self.reqRuleId()
        id=616
        # 通过围栏id，查找围栏违规数据 查找出详细的违规数据 包括违规开始时间和结束时间 违规的id
        sql = ("select device_id,crt_time,end_time,TIMESTAMPDIFF(SECOND,crt_time,end_time) as time "
               "from {}.vlt_fouls WHERE rule_id={} ORDER BY device_id,crt_time ").format(self.db,id)
        print(sql)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        print(result)
        return result

    def reqVltDetails2(self,deviceId,id):  #显示单个id的违规情况  电子围栏违规
        #id = self.reqRuleId()
        #id=616
        # 通过围栏id，查找围栏违规数据 查找出详细的违规数据 包括违规开始时间和结束时间 违规的id
        sql = ("select device_id,crt_time,end_time,TIMESTAMPDIFF(SECOND,crt_time,end_time) as time "
               "from {}.vlt_fouls WHERE rule_id={} and device_id=\'{}\'").format(self.db,id,deviceId)
        #print(sql)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        #print(result)
        return result

    def reqVltBehavior(self,deviceId, t1, t2):  #获取单个id 在一段时间内的 信号消失情况 t1开始时间 t2结束时间
        sql = (
            "SELECT violation_time as crt_time ,discovery_time as end_time, ABS(TIMESTAMPDIFF(SECOND,violation_time,discovery_time)) as time "
            "FROM vlt_behavior_abnormal WHERE device_id=\'{}\' AND create_time BETWEEN \'{}\' AND \'{}\' AND abnomal_type=0".format(deviceId,t1,t2,deviceId)
        )
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        return result


    def ruleTime(self,id): #获取围栏的开始时间和结束时间  以及计算出围栏的生效时间
        sql = ('SELECT start_time,invalid_time,update_time,TIMESTAMPDIFF(SECOND,start_time,invalid_time) as t1,TIMESTAMPDIFF(SECOND,start_time,update_time) as t2 FROM `map_pos_rules` WHERE id ={}'.format(id))
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        start_time = result[0][0]
        invalid_time = result[0][1]
        update_time = result[0][2]
        t1 = result[0][3] #差值
        t2 = result[0][4]
        return start_time,invalid_time,update_time,t1,t2




    def closeConnection(self):
        self.cursor.close()

    #修改安全帽的类型,只要删除map_device_software表数据即可
    def changeDeviceType(self,ids):
        for id in ids:
            #print(id)
            #先查询id是否存在 如果存在再做删除操作
            sql = "SELECT software_device_type FROM map_device_software where device_id =\"{}\";".format(id[0])
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            #print(result)
            if len(result) != 0:
                print(id[0]+"存在并且type为2，执行删除操作")
                sql_delete = "DELETE FROM map_device_software WHERE device_id=\"{}\";".format(id[0])
                #print(sql_delete)
                self.cursor.execute(sql_delete) #执行sql
                self.conn.commit() #提交事务
                print(self.cursor.rowcount,"删除成功")

            else:
                print(id[0]+"在map_device_software中type不是2，无需删除")


    #统计当前的在线设备数
    def onlineCount(self,t,t1):  #t运行时间，t1是间隔多长统计一次
        num = (t*60)/t1
        list = []
        while num>0:
            time.sleep(t1)
            sql = "SELECT COUNT(*) FROM `map_device` WHERE device_status=1 and device_type=1 AND device_id not LIKE 'A%'"
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            print(result[0][0])
            list.append(result[0][0])
            num = num-1
        #print(list)

        avr = mean(list)  #求平均
        print("平均值："+str(avr))
        return avr

    #统计静止的过程中 静止状态占比是多少

    def stopOrmove(self,surface,device,t1,t2): #传入表名
        sql = ("SELECT SUM( CASE WHEN device_status = 1 THEN 1 ELSE 0 END ) AS status_1,"
               "SUM( CASE WHEN device_status = - 1 THEN 1 ELSE 0 END ) AS status_minus_1,"
               "SUM( CASE WHEN device_status = 0 THEN 1 ELSE 0 END ) AS status_0,"
               "SUM( CASE WHEN device_status = 32 THEN 1 ELSE 0 END ) AS status_32,"
               "(SUM( CASE WHEN device_status = - 1 THEN 1 ELSE 0 END ) / COUNT(*)) AS probability_of_minus_1 "
               "FROM {}"
               " WHERE device_id = \"{}\" AND update_time BETWEEN '{}' AND '{}'"
               .format(surface,device,t1,t2))
        #print(sql)

        self.cursor.execute(sql)  #执行sql
        result = self.cursor.fetchall()  #获取数据
        status_1 = result[0][0]
        status_minus_1 = result[0][1]
        status_0 = result[0][2]
        status_32 = result[0][3]
        probability_of_minus_1 = result[0][4]
        return status_1,status_minus_1,status_0,status_32,probability_of_minus_1

    def queryMome(self,deviceid):
        sql = "SELECT memo FROM map_device where device_id = \"{}\";".format(deviceid)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        memo = result[0][0]
        return memo




if __name__ == '__main__':
   host = '192.168.3.44'
   #host = 'localhost'
   port = 3306
   user='test123'
   password='test1234'
   db='gwza0507'
   #db = 'gwza_refactory_local'
   #name = "警告型 — 禁止入内"
   #path = os.path.dirname(__file__)
   #print(path)
   #surface = 'map_device_his0401_11_10'


   ob = sqlOperate(host, port, user, password, db)
   ob.sqlConnect()
   device = 'GW4E33'
   res = ob.queryMome(device)
   print(res)
   # t1 = '2024-04-02 00:00:00'
   # t2 = '2024-04-02 00:30:00'
   # status_1,status_minus_1,status_0,status_32,probability_of_minus_1 = ob.stopOrmove(surface,device,t1,t2)
   # print(status_1,status_minus_1,status_0,status_32,probability_of_minus_1)


   # t1 = '2024-01-10 11:15:13'
   # t2 = '2024-03-13 17:10:06'
   # result = ob.reqVltBehavior(device,t1,t2)
   # print(len(result))
   # for t1, t2, con_t in result:
   #     print(t1, t2, con_t)
   # for res in result:
   #     print(res[0],res[1],res[2])
   #ob.reqVltNum()
   #获取违规信息统计
   # id = ob.reqRuleId(name)
   # start_time,invalid_time,update_time,t1,t2 = ob.ruleTime(id)
   # print(start_time, invalid_time, update_time, t1, t2)
   #
   # if invalid_time>update_time and t1>t2:
   #     print(start_time,invalid_time,update_time,t1,t2)

   #rfr.writeTxt(ob,id,name)
   # 修改安全帽类型
   # df = pd.read_excel('../config/data.xls', sheet_name='Sheet1', header=0)
   # values = df.values
   # ob.changeDeviceType(values)

   #获取一段时间内 总在线人数的平均值
   #ob.onlineCount(20,2) #t运行时间，t1是间隔多长统计一次

   ob.closeConnection()
